package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.SystemDictConstant;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IAgencyVerifyDao;
import com.boss.salary.entity.AgencyExt;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.salary.entity.SalaryRental;
import com.boss.salary.vo.unitReportVO;
import com.boss.sys.ca.user.dto.UserDTO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Repository
public class AgencyVerifyDaoImpl extends BaseDaoImpl implements IAgencyVerifyDao {


    @Override
    public void saveAgencyVerify(AgencyVerify agencyVerify) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_VERIFY);
        sql.append("(id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code, ")
                .append("salary_info_verified_code, person_info_verified_code, agency_info_verified_code, ")
                .append("is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, ")
                .append("is_confirm_submit, is_month_end, is_deleted, is_salary_change, add_person_num, delete_person_num, is_thirteen, is_auto");
        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                agencyVerify.getId(), agencyVerify.getBizKey(), agencyVerify.getCreateTime(), agencyVerify.getUpdateTime(),
                agencyVerify.getAgencyId(), agencyVerify.getSalaryMonth(), agencyVerify.getSalaryVerifiedInAgencyCode(),
                agencyVerify.getSalaryInfoVerifiedCode(), agencyVerify.getPersonInfoVerifiedCode(),
                agencyVerify.getAgencyInfoVerifiedCode(), agencyVerify.getIsSubmitInAgency(), agencyVerify.getIsSalarySubmit(),
                agencyVerify.getIsPersonSubmit(), agencyVerify.getIsAgencySubmit(), agencyVerify.getIsConfirmSubmit(),
                agencyVerify.getIsMonthEnd(), agencyVerify.getIsDeleted(), agencyVerify.getIsSalaryChange(),
                agencyVerify.getAddPersonNum(), agencyVerify.getDeletePersonNum(), agencyVerify.getIsThirteen(),SystemDictConstant.IS_FALSE
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateAgencyVerify(AgencyVerify agencyVerify) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" update_time = ? ");
        paramList.add(String.valueOf(DateUtils.getCurrentTimeStamp()));
        // 单位id
        if (agencyVerify.getAgencyId() != null) {
            sql.append(", agency_id = ? ");
            paramList.add(agencyVerify.getAgencyId());
        }
        // 报审月份
        if (agencyVerify.getSalaryMonth() != null) {
            sql.append(", salary_month = ? ");
            paramList.add(agencyVerify.getSalaryMonth());
        }
        // 机构内部工资审核状态
        if (agencyVerify.getSalaryVerifiedInAgencyCode() != null) {
            sql.append(", salary_verified_in_agency_code = ? ");
            paramList.add(agencyVerify.getSalaryVerifiedInAgencyCode());
        }
        // 工资信息审核状态
        if (agencyVerify.getSalaryInfoVerifiedCode() != null) {
            sql.append(", salary_info_verified_code = ? ");
            paramList.add(agencyVerify.getSalaryInfoVerifiedCode());
        }
        // 人员信息审核状态
        if (agencyVerify.getPersonInfoVerifiedCode() != null) {
            sql.append(", person_info_verified_code = ? ");
            paramList.add(agencyVerify.getPersonInfoVerifiedCode());
        }
        // 机构信息审核状态
        if (agencyVerify.getAgencyInfoVerifiedCode() != null) {
            sql.append(", agency_info_verified_code = ? ");
            paramList.add(agencyVerify.getAgencyInfoVerifiedCode());
        }
        // 单位内部工资是否送审
        if (agencyVerify.getIsSubmitInAgency() != null) {
            sql.append(", is_submit_in_agency = ? ");
            paramList.add(agencyVerify.getIsSubmitInAgency());
        }
        // 工资信息是否上报
        if (agencyVerify.getIsSalarySubmit() != null) {
            sql.append(", is_salary_submit = ? ");
            paramList.add(agencyVerify.getIsSalarySubmit());
        }
        // 人员信息是否上报
        if (agencyVerify.getIsPersonSubmit() != null) {
            sql.append(", is_person_submit = ? ");
            paramList.add(agencyVerify.getIsPersonSubmit());
        }
        // 机构信息是否上报
        if (agencyVerify.getIsAgencySubmit() != null) {
            sql.append(", is_agency_submit = ? ");
            paramList.add(agencyVerify.getIsAgencySubmit());
        }
        // 是否确认上报
        if (agencyVerify.getIsConfirmSubmit() != null) {
            sql.append(", is_confirm_submit = ? ");
            paramList.add(agencyVerify.getIsConfirmSubmit());
        }
        // 是否已月结
        if (agencyVerify.getIsMonthEnd() != null) {
            sql.append(", is_month_end = ? ");
            paramList.add(agencyVerify.getIsMonthEnd());
        }
        // 如果id不为空，根据id更新
        if (!StringUtils.isEmpty(agencyVerify.getId())) {
            sql.append(" where id = ? ");
            paramList.add(agencyVerify.getId());
        }
        // 如果单位信息不为空，根据单位id更新
        else if (!StringUtils.isEmpty(agencyVerify.getAgencyId())) {
            sql.append(" where agency_id = ? and is_month_end = '0' and is_thirteen = '0'");
            paramList.add(agencyVerify.getAgencyId());
        }
        this.getJdbcTemplate().update(sql.toString(), paramList.toArray());
    }

    @Override
    public void updateAgencyState(AgencyVerify agencyVerify,String mof_div_code) {
        StringBuilder sb = new StringBuilder();
        List<Object> params = new ArrayList<>();
        sb.append(" update ")
                .append(TableName.AGENCY_VERIFY)
                .append(" set ").append(" update_time = ? ");
        params.add(agencyVerify.getUpdateTime());
        if(!StringUtils.isEmpty(agencyVerify.getIsSalaryChange())){
            sb.append(" , is_salary_change = ? ");
            params.add(agencyVerify.getIsSalaryChange());
        }
        if (!StringUtils.isEmpty(agencyVerify.getIsSubmitInAgency())) {
            sb.append(" , is_submit_in_agency = ? ");
            params.add(agencyVerify.getIsSubmitInAgency());
        }
        if (!StringUtils.isEmpty(agencyVerify.getIsSubmitInAgency())) {
            sb.append(" , is_salary_submit = ? ");
            params.add(agencyVerify.getIsSalarySubmit());
        }
        if(!StringUtils.isEmpty(agencyVerify.getSalaryVerifiedInAgencyCode())){
            sb.append(" , salary_verified_in_agency_code = ? ");
            params.add(agencyVerify.getSalaryVerifiedInAgencyCode());
        }
        if(!StringUtils.isEmpty(agencyVerify.getIsBankDataCreated())){
            sb.append(" , is_bank_data_created = ? ");
            params.add(agencyVerify.getIsBankDataCreated());
        }
        sb.append(" where ")
                .append(" is_deleted = 0 and is_month_end = 0  and mof_div_code=? ");
        params.add(mof_div_code);
        if(!StringUtils.isEmpty(agencyVerify.getIsThirteen())){
            sb.append(" and is_thirteen = ? ");
            params.add(agencyVerify.getIsThirteen());
        }
//        if(!StringUtils.isEmpty(agencyVerify.getAgencyId())){
//            sb.append(" and agency_id = ? ");
//            params.add(agencyVerify.getAgencyId());
//        }
        if(SystemDictConstant.IS_TRUE==agencyVerify.getIsBankDataCreated()){
            sb.append(" and is_pay_data_created = 1 ");
        }
        super.getjdbcTemplate().update(sb.toString(), params.toArray());
    }


    @Override
    public Map<String, Object> selectStateByAgencyId(String agencyId, String... columnNames) {
        if(columnNames == null || columnNames.length == 0){
            return Collections.emptyMap();
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (int i = 0; i < columnNames.length; i++) {
            sb.append(columnNames[i]);
            if (i != columnNames.length - 1) {
                sb.append(" , ");
            }
        }
        sb.append(" from ")
                .append(TableName.AGENCY_VERIFY)
                .append(" where ")
                .append(" agency_id = ? ")//原方法存在and is_deleted = 0条件，现功能存在单个人员单位做调出消减后单位会被修改为删除状态，会导致上传附件查不到数据，将改删除状态从查询中拿掉，不影响业务
                .append(" and is_month_end = 0 and is_thirteen = 0 ");
//        如果不是叶子机构，会查不到数据。
        try{
            return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId);
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @Override
    public Map<String, Object> selectStateByAgencyIdAndBizkey(String agencyId,List<String> bizkeys,String flag, String... columnNames) {
        if(null == columnNames   || columnNames.length == 0){
            return Collections.emptyMap();
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" select ");
        for (int i = 0; i < columnNames.length; i++) {
            sb.append(columnNames[i]);
            if (i != columnNames.length - 1) {
                sb.append(" , ");
            }
        }
        sb.append(" from ")
                .append(TableName.AGENCY_VERIFY)
                .append(" where ")
                .append(" is_deleted = 0 and agency_id = ? ")
                .append(" and is_month_end = 0 ");
        if(!"all".equals(flag)) {
            sb.append(" and ").append(SysUtils.inSql("biz_key", bizkeys));
        }
//        如果不是叶子机构，会查不到数据。
        try{
            return super.getjdbcTemplate().queryForMap(sb.toString(), agencyId);
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @Override
    public void batchUpdateMonthEnd(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ")
                .append(" is_month_end = ?, update_time = ?");
        sql.append(" where agency_id = ? and salary_month = ?");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 4; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchInsertInitDate(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into ").append(TableName.AGENCY_VERIFY)
                .append(" ( id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code, ")
                .append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code, is_submit_in_agency, ")
                .append(" is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit, is_month_end, is_deleted, ")
                .append(" audit_explain, remark, is_salary_change, add_person_num, add_quit_person_num, delete_person_num, ")
                .append(" is_thirteen, is_pay_data_created, is_bank_data_created,mof_div_code, year,is_auto,agency_name,agency_code ) ")
                .append(" values (")
                .append("?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 31; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteNoMonthEnd(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" DELETE FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" WHERE is_month_end = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE});
    }

    @Override
    public void updateAlreadyMonthEnd(String month, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" UPDATE ").append(TableName.AGENCY_VERIFY).append(" SET ");
        sql.append(" is_month_end = ? ");
        sql.append(" WHERE salary_month = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        this.getJdbcTemplate().update(sql.toString(), new Object[]{SystemDictConstant.IS_FALSE, month});
    }

    @Override
    public void updateSalaryVerify(List<Object[]> params) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ")
                .append(" is_submit_in_agency=? , is_salary_submit = ?, is_auto = ? ");
        sql.append(" where agency_id = ? and is_month_end = ?");
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 5; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public List<AgencyVerify> getAgencyVerifyByAgencyIds(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen, audit_explain,is_pay_data_created,is_auto, agency_code, agency_name ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = 0 ");
        if (!StringUtils.isEmpty(agencyIds) && agencyIds.size() > 0) {
            sql.append(" and ").append(com.boss.sys.utils.SysUtils.inSql("agency_id", agencyIds));
        }
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyVerify.class));
    }

    @Override
    public void updateChangePersonNum(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update  ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" is_salary_change = is_salary_change + 1");
        sql.append(" where agency_id = ? and is_month_end = ? and is_thirteen = ?");
        this.getJdbcTemplate().update(sql.toString(), new Object[]{agencyId, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE});
    }

    @Override
    public void updateAddPersonNum(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update  ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" add_person_num = add_person_num + 1");
        sql.append(" where agency_id = ? and is_month_end = ? and is_thirteen = ?");
        this.getJdbcTemplate().update(sql.toString(), new Object[]{agencyId, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE});
    }

    @Override
    public void updateAddQuitPersonNum(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update  ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" add_quit_person_num = add_quit_person_num + 1");
        sql.append(" where agency_id = ? and is_month_end = ? and is_thirteen = ?");
        this.getJdbcTemplate().update(sql.toString(), new Object[]{agencyId, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE});
    }

    @Override
    public void updateDeletePersonNum(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update  ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" delete_person_num = delete_person_num + 1");
        sql.append(" where agency_id = ? and is_month_end = ? and is_thirteen = ?");
        this.getJdbcTemplate().update(sql.toString(), new Object[]{agencyId, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE});
    }

    @Override
    public AgencyVerify getInfoByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen, is_auto,year ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and is_thirteen = ?");
        sql.append(" and agency_id = ?");
        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class),
                    new Object[]{SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE,
                    agencyId});
        } catch (EmptyResultDataAccessException e) {
           return  new AgencyVerify();
        }
    }
    @Override
    public SalaryRental fiscalCombined( Map<String, Object>queryParam, List<String> bizKeys,List<String> childrenId) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select case when sum(b.s1) is null then 0 else sum(b.s1) end  sfgzze, ")
                .append(" case  when sum(b.s2) is null then 0 else sum(b.s2) end yfgzze ");

        sql.append(" from ").append(TableName.SALARY_MONTH_END_SUBMIT).append(" as b")
                .append(" join ").append(TableName.PERSON_EXT_SUBMIT).append(" as pe ")
                .append(" on  b.person_id=pe.person_id and b.agency_id=pe.agency_id ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as c")
                .append(" on b.agency_id=c.agency_id and pe.agency_id=c.agency_id ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" as a")
                .append(" on a.id=c.agency_id ")

                .append(" where a.is_deleted=? and c.is_deleted=? AND pe.`is_deleted` = 0 ")
                .append(" and c.is_thirteen=? and pe.is_stop_salary =? ")
                .append(" and c.is_month_end=? ");
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.IS_FALSE);
        paramList.add(SystemDictConstant.DISABLE);
        paramList.add(SystemDictConstant.DISABLE);
       // paramList.add(SystemDictConstant.ENABLE);
        paramList.add(SystemDictConstant.DISABLE);
        sql.append(" and ").append(SysUtils.inSql("c.biz_key", bizKeys));
        sql.append(" and ").append(SysUtils.inSql("b.agency_id",childrenId));
        if(!StringUtils.isEmpty(queryParam)) {
//            sql.append(" and c.is_confirm_submit=").append(queryParam.get("flag"));
          /*  if(!StringUtils.isEmpty(queryParam.get("agencyId"))){
                sql.append(" and a.id = ?");
                paramList.add(queryParam.get("agencyId"));
            }*/
            if(!StringUtils.isEmpty(queryParam.get("dwmc"))){
                sql.append(" and a.agency_name like concat('%',?,'%') ");
                paramList.add(queryParam.get("dwmc"));
            }
            if(!StringUtils.isEmpty(queryParam.get("bddw"))){
                if(queryParam.get("bddw").equals("0")){
                    sql.append(" and c.is_salary_change = 0 ");
                } else {
                    sql.append(" and c.is_salary_change != 0 ");
                }
            }
            if(!StringUtils.isEmpty(queryParam.get("rylx"))){
                sql.append(" and pi.state = ?");
                paramList.add(queryParam.get("rylx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("gzlx"))){
                sql.append(" and pe.merit_pay_code = ? ");
                paramList.add(queryParam.get("gzlx"));
            }
            if(!StringUtils.isEmpty(queryParam.get("mofDivCode"))){
                sql.append(" and a.mof_div_code = ? ");
                paramList.add(queryParam.get("mofDivCode"));
            }
        }
        //sql.append("  group by a.agency_code");

        try {
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(SalaryRental.class), paramList.toArray());
        } catch (EmptyResultDataAccessException e) {
            SalaryRental salaryRental =new SalaryRental();
            salaryRental.setSfgzze("0");
            salaryRental.setYfgzze("0");
            return salaryRental;
        }

    }

    @Override
    public void updateFinancialVerifyStatus(AgencyVerify agencyVerify) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" salary_info_verified_code = ?, is_salary_submit = ?, audit_explain = ? , is_auto = ?, update_time = ?");
        sql.append(" where id = ? ");
        this.getJdbcTemplate().update(sql.toString(), new Object[]{agencyVerify.getSalaryInfoVerifiedCode(),
                agencyVerify.getIsSalarySubmit(), agencyVerify.getAuditExplain(), agencyVerify.getIsAuto(),
                agencyVerify.getUpdateTime(), agencyVerify.getId()});
    }

    @Override
    public void batchUpdateFinancialVerifyStatus(List<AgencyVerify> agencyVerifys) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" salary_info_verified_code = ?, is_salary_submit = ?, audit_explain = ? , is_auto = ?, update_time = ?,is_confirm_submit=?,is_submit_in_agency=?,is_pay_data_created=? ");
        sql.append(" where id = ? ");
        List<Object[]> params=new ArrayList<>();
        for(AgencyVerify agencyVerify:agencyVerifys){
            params.add( new Object[]{agencyVerify.getSalaryInfoVerifiedCode(),
                agencyVerify.getIsSalarySubmit(), agencyVerify.getAuditExplain(), agencyVerify.getIsAuto(),
                agencyVerify.getUpdateTime(),agencyVerify.getIsConfirmSubmit(),agencyVerify.getIsSubmitInAgency(),agencyVerify.getIsPayDataCreated(), agencyVerify.getId()});
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 9; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchUpdateAgencyVerifypayStatus(List<AgencyVerify> agencyVerifys) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" is_pay_data_created = ? ");
        sql.append(" where id = ? ");
        List<Object[]> params=new ArrayList<>();
        for(AgencyVerify agencyVerify:agencyVerifys){
            params.add( new Object[]{
                    0,agencyVerify.getId()});
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


    @Override
    public List<AgencyVerify> getAgencyVerifyBySalaryBank(String bank,String mofDivCode) {
        StringBuilder sql = new StringBuilder();
        List<String> params=new ArrayList<>();
        sql.append("SELECT");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen, audit_explain,is_auto ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = 0 and is_pay_data_created=1 and mof_div_code=? ");
        params.add(mofDivCode);
        sql.append(" and agency_id in (select DISTINCT agency_id from  sal_person_ext_submit where sala_card_bank=? and  mof_div_code=?)");
        params.add(bank);
        params.add(mofDivCode);
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(AgencyVerify.class),params.toArray());
    }
    @Override
    public void batchUpdateAgencyVerifyBankStatus(List<AgencyVerify> agencyVerifys,String status) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" is_bank_data_created = ? ");
        sql.append(" where agency_id = ? and is_month_end=0 ");
        List<Object[]> params=new ArrayList<>();
        for(AgencyVerify agencyVerify:agencyVerifys){
            params.add( new Object[]{
                    status,agencyVerify.getAgencyId()});
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return params.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = params.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }



    @Override
    public int getAddPersonNumByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();
        sql.append("select (add_person_num+add_quit_person_num) from ").append(TableName.AGENCY_VERIFY);
        sql.append(" where agency_id = ? and is_month_end = ? and is_thirteen = ?");
        return getjdbcTemplate().queryForObject(sql.toString(),
                new Object[]{agencyId, SystemDictConstant.IS_FALSE, SystemDictConstant.IS_FALSE}, Integer.class);
    }

    @Override
    public List<AgencyVerify> getAgencyVerifyInfo() {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen, audit_explain");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? ");
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class),
                new Object[]{SystemDictConstant.IS_FALSE});
    }

    @Override
    public List<unitReportVO> selectUnitReportPage(PaginationDTO paginationDTO, String state, List<String> childrenId, String mofDivCode,String is_auto) {
        List<Object> params = new ArrayList<>();
        StringBuffer wheresql=new StringBuffer();
        if("noReport".equals(state)){
            params.add(0);
            params.add(-1);
            params.add(-9);
        }else if("isReport".equals(state)){
            params.add(1);
            if(!StringUtils.isEmpty(is_auto)) {
                if("0".equals(is_auto)) {
                    wheresql.append(" and avy.is_auto=0 ");
                }else if("1".equals(is_auto)){
                    wheresql.append(" and avy.is_auto=1  and avy.is_salary_change=0 ");
                }else{
                    wheresql.append(" and avy.is_auto=1  and avy.is_salary_change=1 ");
                }
            }
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" avy.agency_id,avy.is_salary_submit, avy.update_time, aio.agency_code, aio.agency_name" )
                .append(",case when avy.is_auto=0 then 0 when avy.is_auto=1 and avy.is_salary_change=0 then 1 else 2 end is_auto ")
                .append(",case when avy.is_salary_change<>0 then 1 else 0 end is_salary_change")
                .append(" from ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" aio ")
                .append(" on ").append(" avy.agency_id = aio.id and aio.is_deleted = 0 ")
                .append(" where ").append(" avy.is_deleted = 0 and avy.is_month_end = 0 ").append(wheresql);
        if(!CollectionUtils.isEmpty(params)){
            sb.append(" and avy.is_salary_submit in ( ");
            for(Object param : params){
                if(params.indexOf(param) != params.size() - 1){
                    sb.append(" ?, ");
                }else{
                    sb.append(" ?) ");
                }
            }
        }
        sb.append(" and ").append(SysUtils.inSql("aio.id", childrenId));
        sb.append(" and aio.mof_div_code = ? ");
        params.add(mofDivCode);
        sb.append(" order by avy.is_salary_submit , aio.agency_code");
        String sql = this.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] paramList = this.boxUpParam(params.toArray(), paginationDTO);
        return super.getJdbcTemplate().query(sql, new BeanPropertyRowMapper<>(unitReportVO.class), paramList);
    }

    @Override
    public int selectUnitReportTotal(String state, List<String> childrenId, String mofDivCode,String is_auto) {
        List<Object> params = new ArrayList<>();
        StringBuffer wheresql=new StringBuffer();
        if("noReport".equals(state)){
            params.add(0);
            params.add(-1);
            params.add(-9);
        }else if("isReport".equals(state)){
            params.add(1);
            if(!StringUtils.isEmpty(is_auto)) {
                if("0".equals(is_auto)) {
                    wheresql.append(" and avy.is_auto=0 ");
                }else if("1".equals(is_auto)){
                    wheresql.append(" and avy.is_auto=1  and avy.is_salary_change=0 ");
                }else{
                    wheresql.append(" and avy.is_auto=1  and avy.is_salary_change=1 ");
                }
            }
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" select count(*) ")
                .append(" from ").append(TableName.AGENCY_VERIFY).append(" avy ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" aio ")
                .append(" on ").append(" avy.agency_id = aio.id and aio.is_deleted = 0 ")
                .append(" where ").append(" avy.is_deleted = 0 and avy.is_month_end = 0 ").append(wheresql);
        if(!CollectionUtils.isEmpty(params)){
            sb.append(" and avy.is_salary_submit in ( ");
            for(Object param : params){
                if(params.indexOf(param) != params.size() - 1){
                    sb.append(" ?, ");
                }else{
                    sb.append(" ?) ");
                }
            }
        }
        sb.append(" and ").append(SysUtils.inSql("aio.id", childrenId));
        sb.append(" and aio.mof_div_code = ? ");
        params.add(mofDivCode);
        return super.getJdbcTemplate().queryForObject(sb.toString(), Integer.class, params.toArray());
    }

    @Override
    public AgencyVerify getAgencyVerifyByAgencyId(String agencyId) {
        StringBuilder sql = new StringBuilder();  sql.append(" SELECT ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen");
        sql.append("  ,year  , audit_explain, is_pay_data_created, is_bank_data_created ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = '0' and   agency_id = ? and is_deleted='0' ");//"
        Object[] objects = new Object[]{
                agencyId//,SystemDictConstant.NOT_DELETED  SystemDictConstant.IS_FALSE,
        };
        try{
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
        }catch(EmptyResultDataAccessException e){
            return new AgencyVerify();
        }
    }

    @Override
    public List<AgencyVerify> getSalaryDataByAgencyId(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" id, biz_key, create_time, update_time, agency_id, salary_month, salary_verified_in_agency_code,");
        sql.append(" salary_info_verified_code, person_info_verified_code, agency_info_verified_code,");
        sql.append(" is_submit_in_agency, is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit,");
        sql.append(" is_month_end, is_deleted, remark, is_salary_change, add_person_num, delete_person_num, is_thirteen");
        sql.append("    , audit_explain, is_pay_data_created, is_bank_data_created,is_auto ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = ? and ").append(SysUtils.inSql("agency_id", agencyIds));
        Object[] objects = new Object[]{
                SystemDictConstant.IS_FALSE
        };
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyVerify.class), objects);
    }

    @Override
    public void updateChangeNum(String agencyId, Map<String, String> changeMap) {
        if(!CollectionUtils.isEmpty(changeMap)){
            if(/*!StringUtils.isEmpty(changeMap.get("is_salary_change"))
                    ||*/ !StringUtils.isEmpty(changeMap.get("add_person_num"))
                    || !StringUtils.isEmpty(changeMap.get("add_quit_person_num"))
                    || !StringUtils.isEmpty(changeMap.get("delete_person_num"))){
                StringBuilder sb = new StringBuilder();
                sb.append(" update ").append(TableName.AGENCY_VERIFY)
                        .append(" set ").append(" update_time = ? ");
                String updateTime = String.valueOf(DateUtils.getCurrentTimeStamp());
                /*if(!StringUtils.isEmpty(changeMap.get("is_salary_change"))){
                    String code = changeMap.get("is_salary_change");
                    sb.append(" , is_salary_change = is_salary_change ").append(code);
                }*/
                if(!StringUtils.isEmpty(changeMap.get("add_person_num"))){
                    String code = changeMap.get("add_person_num");
                    sb.append(" , add_person_num = add_person_num ").append(code);
                }
                if(!StringUtils.isEmpty(changeMap.get("add_quit_person_num"))){
                    String code = changeMap.get("add_quit_person_num");
                    sb.append(" , add_quit_person_num = add_quit_person_num ").append(code);
                }
                if(!StringUtils.isEmpty(changeMap.get("delete_person_num"))){
                    String code = changeMap.get("delete_person_num");
                    sb.append(" , delete_person_num = delete_person_num ").append(code);
                }
                sb.append(" where ").append(" is_month_end = 0 and agency_id = ? ");
                super.getjdbcTemplate().update(sb.toString(), updateTime, agencyId);
            }
        }
    }

    @Override
    public List<Map<String,Object>> selectOverlapPersionIds(String mof_div_code) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select a.person_id from  ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" as a ")
                .append(" join ").append(TableName.SALARY_MONTH_BEGIN).append(" as b ")
                .append("  on a.person_id=b.person_id and a.agency_id<>b.agency_id  ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as c ")
                .append("  on b.agency_id=c.agency_id and c.is_salary_submit<>1 and c.is_month_end=0 ")
                .append(" where a.mof_div_code = ?");

        List<Map<String,Object>> result = this.getjdbcTemplate().queryForList(sb.toString(),mof_div_code);

        return result;
    }

    @Override
    public List<Map<String,Object>> selectOverlapPersionInfo(String mov_div_code) {
        StringBuilder sql = new StringBuilder();
        sql.append("select f.person_name,f.iden_no,d.agency_code,d.agency_name,")
                .append("e.agency_code unsubagencycode,e.agency_name unsubagencyname,e.id unsubagencyid from ")
                .append(TableName.SALARY_MONTH_END_SUBMIT).append(" as a ")
                .append(" join ").append(TableName.SALARY_MONTH_BEGIN).append(" as b ")
                .append("  on a.person_id=b.person_id and a.agency_id<>b.agency_id  ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" as c ")
                .append("  on b.agency_id=c.agency_id and c.is_salary_submit<>1 and c.is_month_end=0 ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" as d")
                .append("  on a.agency_id=d.id and d.is_deleted=0 ")
                .append(" join ").append(TableName.AGENCY_INFO).append(" as e")
                .append("  on b.agency_id=e.id and e.is_deleted=0 ")
                .append(" join ").append(TableName.PERSON_INFO).append(" as f")
                .append("  on a.person_id= f.id ")
                .append(" where a.mof_div_code=? ");

        return this.getjdbcTemplate().queryForList(sql.toString(),mov_div_code);
    }

    @Override
    public String getSalaryMonth(String province,String agencyId) {
        List<Object> params=new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ");
        sql.append(" max(salary_month) salary_month ");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        /*sql.append(" where MOF_DIV_CODE = ?  ");//
        sql.append("and is_month_end= 0 ");
        //sql.append("and is_deleted= ?");
         params.add(province);
        // params.add(SystemDictConstant.NOT_DELETED);
        if(agencyId!=null) {
            sql.append(" and AGENCY_ID=? ");
            params.add(agencyId);
        }*/
        try{
            return super.getJdbcTemplate().queryForObject(sql.toString(), String.class, params.toArray());
        }catch(EmptyResultDataAccessException e){
            return "";
        }
    }

    @Override
    public Map<String, Object> checkAgencyIsChanged(String agencyId) {
        List<Object> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
            .append(" joinconut \"joinconut\",nowcount \"nowcount\" ")
        .append(" from ")
        .append(" (select count(spe.id) joinconut from ").append(TableName.PERSON_EXT).append("  spe ")
                .append(" join ").append(TableName.SAL_PERSON_EXT_BEGIN).append("  spebegin ")
                .append(" on spe.agency_id=spebegin.agency_id and spe.person_id=spebegin.person_id where spe.agency_id=? and spebegin.agency_id= ?)  c,");
        params.add(agencyId);
        params.add(agencyId);
        sql.append(" (select count(spe.id) nowcount  from ").append(TableName.PERSON_EXT).append(" spe  where is_deleted=0 and spe.agency_id=? )  d ");
        params.add(agencyId);
        return super.getJdbcTemplate().queryForMap(sql.toString(), params.toArray());

    }
    @Override
    public List<AgencyVerify> queryAgencyIfo(List<AgencyVerify> agencyInfoList, UserDTO userDTO) {
       List<String> ParamsList=new ArrayList<>();
       for(AgencyVerify agencyVerify:agencyInfoList){
           ParamsList.add(agencyVerify.getAgencyId());
       }
        StringBuilder sql = new StringBuilder();
        sql.append("select  id as agencyId ,agency_code,agency_name from ").append(TableName.AGENCY_INFO)
                .append(" where is_deleted=0 and  ");
        sql.append(SysUtils.inSql("id",ParamsList));
        return this.getjdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper(AgencyVerify.class));
    }

    @Override
    public void insertAgencyInfo(List<AgencyVerify> agencyVerifyList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" merge  into ").append(TableName.AGENCY_INFO)
                .append(" a using " )
                .append( " (select  ? id,? biz_key, ? create_time,? agency_code, ? agency_name,? mof_div_code,? is_leaf, ? is_deleted,? parent_id,? level_no,? is_payment_agency from dual ) b " )
                .append(" on (a.id = b.id) " )
                .append(" WHEN MATCHED THEN " )
                .append("   UPDATE SET a.update_time=b.create_time,a.agency_name = b.agency_name,a.agency_code=b.agency_code,a.is_leaf=b.is_leaf,a.parent_id=b.parent_id,a.level_no=b.level_no,a.is_deleted=b.is_deleted  " )
                .append( " WHEN NOT MATCHED THEN " )
                .append( "   INSERT " )
                .append( "    (a.id, a.biz_key, a.create_time, a.agency_code,a.agency_name, a.mof_div_code, a.is_leaf,  a.is_deleted,a.parent_id,a.level_no,a.is_payment_agency) ")
                .append( "   VALUES " )
                .append("     (b.id, b.biz_key, b.create_time, b.agency_code,b.agency_name, b.mof_div_code, b.is_leaf,  b.is_deleted,b.parent_id,b.level_no,b.is_payment_agency)");
        List<Object[]> paramsList = new ArrayList<>(agencyVerifyList.size());
        for (AgencyVerify agencyVerify : agencyVerifyList) {
            Object[] params = new Object[]{
                    agencyVerify.getAgencyId(),agencyVerify.getAgencyId(),agencyVerify.getCreateTime(),
                    agencyVerify.getAgencyCode(), agencyVerify.getAgencyName(),
                    agencyVerify.getMofDivCode(), agencyVerify.getIsLeaf(), agencyVerify.getIsDeleted(),
                    agencyVerify.getParentId(),agencyVerify.getLevelNo(),agencyVerify.getIsPaymentAgency()
            };
            paramsList.add(params);
        }
        /*super.getJdbcTemplate().batchUpdate(sql.toString(), paramsList);
        StringBuilder sql = new StringBuilder();
        sql.append(" insert  into ").append(TableName.AGENCY_INFO);
        sql.append("( id, biz_key, create_time, agency_code,agency_name, ")
                .append("mof_div_code, is_leaf,  is_deleted,")
                .append("parent_id,level_no,is_payment_agency)")
                .append(" values (?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramsList = new ArrayList<>(agencyVerifyList.size());
        for (AgencyVerify agencyVerify : agencyVerifyList) {
            Object[] params = new Object[]{
                    agencyVerify.getAgencyId(),agencyVerify.getAgencyId(),agencyVerify.getCreateTime(),
                    agencyVerify.getAgencyCode(), agencyVerify.getAgencyName(),
                    agencyVerify.getMofDivCode(), agencyVerify.getIsLeaf(), agencyVerify.getIsDeleted(),
                    agencyVerify.getParentId(),agencyVerify.getLevelNo(),agencyVerify.getIsPaymentAgency()
            };
            paramsList.add(params);
        }*/
        super.getJdbcTemplate().batchUpdate(sql.toString(),  new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a <= 10; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void insertAgencyVerify(List<AgencyVerify> agencyVerifyList) {

        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.AGENCY_VERIFY);
        sql.append("( id, create_time, agency_id," )
                .append(  " salary_verified_in_agency_code, salary_info_verified_code, person_info_verified_code, agency_info_verified_code, is_submit_in_agency," )
                .append(  " is_salary_submit, is_person_submit, is_agency_submit, is_confirm_submit, is_deleted, " )
                .append( "audit_explain, remark, is_salary_change, add_person_num, add_quit_person_num, " )
                .append("delete_person_num, is_thirteen, is_pay_data_created, is_bank_data_created, mof_div_code, " )
                .append( "year, is_auto, is_verify_end, agency_code, agency_name,salary_month,is_month_end,biz_key)")
                .append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        List<Object[]> paramsList = new ArrayList<>(agencyVerifyList.size());
        for (AgencyVerify agencyVerify : agencyVerifyList) {
            Object[] params = new Object[]{
                    agencyVerify.getId(),agencyVerify.getCreateTime(),agencyVerify.getAgencyId(),
                    agencyVerify.getSalaryVerifiedInAgencyCode(),agencyVerify.getSalaryInfoVerifiedCode(),agencyVerify.getPersonInfoVerifiedCode(),agencyVerify.getAgencyInfoVerifiedCode(),agencyVerify.getIsSubmitInAgency(),
                    agencyVerify.getIsSalarySubmit(),agencyVerify.getIsPersonSubmit(),agencyVerify.getIsAgencySubmit(),agencyVerify.getIsConfirmSubmit(),agencyVerify.getIsDeleted(),
                    agencyVerify.getAuditExplain(),agencyVerify.getRemark(),agencyVerify.getIsSalaryChange(),agencyVerify.getAddPersonNum(),agencyVerify.getAddQuitPersonNum(),
                    agencyVerify.getDeletePersonNum(),agencyVerify.getIsThirteen(),agencyVerify.getIsPayDataCreated(),agencyVerify.getIsBankDataCreated(),agencyVerify.getMofDivCode(),
                    agencyVerify.getYear(),agencyVerify.getIsAuto(),agencyVerify.getIsVerifyEnd(),agencyVerify.getAgencyCode(),agencyVerify.getAgencyName(),agencyVerify.getSalaryMonth(),agencyVerify.getIsMonthEnd(),agencyVerify.getBizKey()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 31; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void insertAgencyExt(List<AgencyVerify> agencyVerifyList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" merge  into ").append(TableName.AGENCY_EXT)
                .append(" a using " )
                .append( " (select ? id,? create_time,? agency_id,? mof_div_code,? is_leaf,? is_deleted  from dual ) b " )
                .append(" on (a.agency_id = b.agency_id) " )
                .append(" WHEN MATCHED THEN " )
                .append("   UPDATE SET a.is_leaf=b.is_leaf,a.is_deleted=b.is_deleted " )
                .append( " WHEN NOT MATCHED THEN " )
                .append( "   INSERT " )
                .append( "    (a.id,a.agency_id,a.create_time,a.mof_div_code,a.is_leaf, a.is_deleted,a.VERIFY_STATE) ")
                .append( "   VALUES " )
                .append("     (b.id,b.agency_id,b.create_time,b.mof_div_code,b.is_leaf, b.is_deleted,'2')");

        List<Object[]> paramsList = new ArrayList<>(agencyVerifyList.size());
        for (AgencyVerify agencyVerify : agencyVerifyList) {
            Object[] params = new Object[]{
                    agencyVerify.getId(),agencyVerify.getCreateTime(),
                    agencyVerify.getAgencyId(), agencyVerify.getMofDivCode(), agencyVerify.getIsLeaf(), agencyVerify.getIsDeleted()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 6; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }


    @Override
    public List<PersonExt> selectPersonExtByAgencyId(String agencyId){
        // List<String> params = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select person_name, sala_card_bank from ")
                .append(TableName.PERSON_EXT)
                .append(" where is_deleted = 0 and agency_id = ? ");
        //params.add(agencyId);
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<>(PersonExt.class),agencyId);

    }

    @Override
    public AgencyExt selectAgencyExtByAgencyId(String agencyId){
        StringBuilder sql = new StringBuilder();
        sql.append("select agency_bank_name from ")
                .append(TableName.AGENCY_EXT)
                .append(" where is_deleted = 0 and agency_id = ? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(AgencyExt.class),agencyId);
    }

    @Override
    public List<Map<String, Object>> selectPersonTransfer(String province, String month) {
        StringBuilder sql = new StringBuilder();
        sql.append("select DISTINCT a.person_name \"person_name\" ,a.iden_no \"iden_no\",c.agency_code \"agency_code\" ,c.agency_code || '-' || c.agency_name \"agency\",d.agency_code || '-' || d.agency_name \"outtoagency\"  ")
                .append(" from ")
                .append(TableName.PERSON_OPERATE).append(" a ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" c ")
                .append("  ON a.agency_id = c.agency_id  ")
                .append(" join ").append(TableName.AGENCY_VERIFY).append(" d ")
                .append("  ON a.out_to_agency = d.agency_id ")
                .append(" LEFT join ").append(TableName.PERSON_OPERATE).append(" b ")
                .append("  ON b.operate_code = 'in' ")
                .append("  AND a.person_id = b.person_id  AND a.out_to_agency = b.agency_id  AND b.verify_state = 2 ")
                .append(" where a.operate_code = 'out'  and b.id IS NULL  ")
                .append(" AND a.mof_div_code =?  and c.is_month_end = '0' ")
                .append(" AND a.salary_month =? ")
                .append("ORDER BY c.agency_code");
        Object[] params = new Object[]{
                province,month
        };
        return this.getjdbcTemplate().queryForList(sql.toString(),params);
    }

    @Override
    public void revokeGenerate(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY).append(" set ");
        sql.append(" is_bank_data_created = '0' ");
        sql.append(" where is_bank_data_created = '1' and is_month_end = 0 and mof_div_code = ?");
        this.getJdbcTemplate().update(sql.toString(),province);
    }

    @Override
    public String currentMonth(String province ) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" max(salary_month)");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = 0  and mof_div_code = ? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class,province);
    }

    @Override
    public String currentYear(String province ) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" max(year)");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = 0  and mof_div_code = ? ");
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class,province);
    }

    @Override
    public String currentYear(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT");
        sql.append(" max(year)");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = 0  and  ").append(SysUtils.inSql("agency_id",agencyIds));
        return this.getJdbcTemplate().queryForObject(sql.toString(), String.class);
    }

    @Override
    public void updateAgencyVerifyCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.AGENCY_VERIFY);
        sql.append(" set  agency_code=?,agency_name =?  where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify agencyVerify: updateList) {
            Object[] params = new Object[]{
                    agencyVerify.getAgencyCode(),agencyVerify.getAgencyName(),agencyVerify.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public List<AgencyVerify> queryAgencyVerify(List<AgencyVerify> agencyVerifies) {
        StringBuilder sql = new StringBuilder();
        List<String> bizKeyList=new ArrayList<>();
        for(AgencyVerify agencyVerifie:agencyVerifies){
            bizKeyList.add(agencyVerifie.getBizKey());
        }
        sql.append("SELECT");
        sql.append(" biz_key,salary_month,agency_id,agency_name");
        sql.append(" FROM ").append(TableName.AGENCY_VERIFY);
        sql.append(" where is_month_end = '1'  and  ");
        sql.append(SysUtils.inSql("biz_key",bizKeyList));
        return this.getJdbcTemplate().query(sql.toString(),new BeanPropertyRowMapper<>(AgencyVerify.class));
    }

}
